#!/usr/bin/env python
# coding: utf-8

# In[88]:


import pandas as pd 
import pymysql

# 获取用户信息
try:
    conn = pymysql.connect(host="localhost", user="test", passwd="123456", db='course', charset="utf8")
    cursor = conn.cursor()
    print("数据库连接成功！")
except Exception as e:
    print(e)

sql1 = 'select * from 6_task_province_gdp;'  # 各省GDP指数
sql2 = "select Idx, UserInfo_19, target from 6_task_training_master;"
try:
    cursor.execute(sql1)
    res = cursor.fetchall()
    cols = [cursor.description[i][0] for i in range(len(cursor.description))]
    data = pd.DataFrame(res, columns=cols)
    
    cursor.execute(sql2)
    res = cursor.fetchall()
    cols = [cursor.description[i][0] for i in range(len(cursor.description))]
    data2 = pd.DataFrame(res, columns=cols)
    print("数据获取成功！")
    cursor.close()
    conn.commit()
    conn.close()
except Exception as e:
    print(e)
    

import argparse
import numpy as np

# 初始化参数构造器
parser = argparse.ArgumentParser()

# 在参数构造器中添加两个命令行参数
parser.add_argument('--filename', type=str,default="所在省份的经济发展影响")

# 获取所有的命令行参数
args = parser.parse_args(args=[])
#args=[]

filename = args.filename # 文件名


# In[94]:


# 数据格式处理
data["province"] = data["province"].map(lambda x:x.strip())  # 去掉空格
data["province"] = data["province"].map(lambda x:"新疆维吾尔自治区" if x == "新疆" else x)
data["province"] = data["province"].map(lambda x:"广西壮族自治区" if x == "广西" else x)
data["province"] = data["province"].map(lambda x:"内蒙古自治区" if x == "内蒙古" else x)
data["province"] = data["province"].map(lambda x:"宁夏回族自治区" if x == "宁夏" else x)
data["province"] = data["province"].map(lambda x:"西藏自治区" if x == "西藏" else x)


# 数据合并
m = pd.merge(left=data2, right=data,left_on="UserInfo_19", right_on="province",how='left')
df_s = m[["Idx", "UserInfo_19", "province", "target","provGDPpp"]]


# 定义计算逾期率函数
def get_p(df):
    num = len(df[df["target"] == 1])
    p = num/len(df)
    return p


# 计算各个省份的逾期率
lst = []
for pro in data.province:
    df_pro = df_s[df_s["province"] == pro]
    p = get_p(df_pro)
    ppGDP = list(data[data["province"] == pro]["provGDPpp"].values)[0]
    lst.append([pro,p, ppGDP])
    
df_yuqi = pd.DataFrame(lst, columns=["省份", "逾期率", "人均GDP"])


# 可视化
import warnings;warnings.filterwarnings("ignore")
from matplotlib import pyplot as plt
import seaborn as sns
plt.rcParams["font.sans-serif"] = ["SimHei"]  # 正常显示中文
plt.rcParams["axes.unicode_minus"] = False # 正常显示负号
import seaborn as sns

fig = plt.figure(figsize=(15,6),dpi=80)
ax1 = fig.add_subplot(111)
ax1.set_ylim([0, 0.14])
ax1.bar(x=df_yuqi["省份"], height=df_yuqi["逾期率"],color="white",edgecolor="black",hatch="/")
ax1.set_ylabel("逾期率", fontdict={"fontsize":15})
ax1.set_xlabel("省份", fontsize=15)
ax1.tick_params(rotation=90)  # 旋转横坐标显示

ax2 = ax1.twinx()
ax2.set_ylim([0, 104000])
ax2.plot(df_yuqi["省份"], df_yuqi["人均GDP"], color="purple")
ax2.set_ylabel("人均GDP", fontsize=15)
# sns.despine(left=True,bottom=True) # 删除边界
# plt.show()
plt.tight_layout()
plt.savefig(filename + ".png")

